package com.example.goods_service.dao;

import com.example.goods_service.vo.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface GoodsDao_oyhl
{
    @Select("SELECT * FROM t_commodity_category where category_name like '%${category_name}%' ")
    List<TCommodityCategory>shop(String category_name);

    @Select("select value,id from search_records where userid='${userid}' ORDER BY time desc")
    List<SearchRecords>ls(int userid);

    @Insert("insert into search_records(value,userid,time) values('${value}','${userid}',NOW())")
    int indata(String value, int userid, String time);

    @Delete("delete from search_records where id='${id}'")
    int dele(int id);

    @Select("select * from search_records where userid='${userid}' and value='${value}'")
    Integer sel(int userid, String value);

    @Select("SELECT DISTINCT b.id,b.commodity_name,b.commodity_description,b.commodity_price,b.status,b.release_time,b.category_id,a.category_name,b.commodity_picture,b.sales_volume FROM `t_commodity_category` a ,t_commodity b where a.id=b.category_id  and a.category_name='${category_name}' and b.status=1 ")
    List<TCommodity>ppp(String category_name);

    @Select("SELECT count(*)  FROM `t_commodity_category` a ,t_commodity b where a.id=b.category_id and a.category_name='${category_name}' ")
    int pppp(String category_name);

    @Select("select category_name from t_commodity_category where  category_name='${category_name}'")
    List<TCommodity> sel1(String category_name);

    @Select("SELECT DISTINCT b.id,b.commodity_name,b.commodity_description,b.commodity_price,b.status,b.release_time,b.category_id,a.category_name,b.commodity_picture,b.sales_volume FROM `t_commodity_category` a ,t_commodity b where a.id=b.category_id  and a.category_name like '%${category_name}%' and b.status=1  ")
    List<TCommodity>ppp1(String category_name);

    @Select("SELECT count(*) FROM `t_commodity_category` a ,t_commodity b where a.id=b.category_id and b.commodity_description like '%${category_name}%' and b.status=1 ")
    int pppp1(String category_name);

    @Select("SELECT b.id , b.attribute from t_commodity_category a ,t_category_attribute b where a.id=b.category_id and category_name='${category_name}'")
    List<TCategoryAttribute>lsal(String category_name);

    @Select("SELECT * FROM t_attribute_details")
    List<TAttributeDetails>list();

    @Select("\n" +
            "SELECT DISTINCT b.id,b.commodity_name,b.commodity_description,b.commodity_price,b.status,b.sales_volume,b.release_time,b.release_time,b.category_id,b.commodity_picture,b.sales_volume" +
            " FROM `t_commodity_category` a ,t_commodity b , t_dianpu_goods c where a.id=b.category_id and b.id = c.goodsid  and  b.release_time like '%${creation_time}%'" +
            " and b.commodity_name like '%${commodity_name}%' and c.userid = #{userid} ORDER BY b.release_time desc ")
    List<TCommodity>ssssss(String creation_time, String commodity_name, int userid);

    @Select("SELECT count(*) FROM `t_commodity_category` a ,t_commodity b,t_dianpu_goods c where a.id=b.category_id  and b.id = c.goodsid and  b.release_time  like '%${creation_time}%' and b.commodity_name like '%${commodity_name}%' and c.userid = #{userid}  ")
    int ssssss1(String creation_time, String commodity_name, int userid);

    @Select("SELECT * FROM t_commodity_allocation")
    List<T_commodity_allocation>sssss();


    @Select("SELECT b.id, b.category_id , b.configuration_type from t_commodity_category a ,t_category_config b where a.id=b.category_id and category_name='${category_name}'")
    List<T_category_config>oooo(String category_name);

    @Select("SELECT * FROM t_config_details")
    List<T_config_details>oooo1();


    //筛选 属性 配置
    @Select(" SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.release_time,a.category_id,e.category_name,a.commodity_picture,a.sales_volume\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and e.category_name = '${category_name}' and a.status=1")
    List<TCommodity>sp(String category_name, String config_details, String attribute);


    @Select("SELECT  count(DISTINCT a.id)\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and e.category_name = '${category_name}' and a.status=1 ")
    int sp1(String category_name, String config_details, String attribute);


    //销量排序
    @Select(" SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.release_time,a.category_id,e.category_name,a.commodity_picture,a.sales_volume\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and a.status=1 and e.category_name = '${category_name}'  ORDER BY a.sales_volume DESC")
    List<TCommodity>px(String category_name, String config_details, String attribute);

    @Select(" SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.release_time,a.category_id,e.category_name,a.commodity_picture,a.sales_volume\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and a.status=1  and e.category_name like '%${category_name}%'  ORDER BY a.sales_volume DESC")
    List<TCommodity>px1(String category_name, String config_details, String attribute);

    //大小价格查询
    @Select(" SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.release_time,a.category_id,e.category_name,a.commodity_picture,a.sales_volume\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and e.category_name = '${category_name}' and a.status=1 and a.commodity_price BETWEEN '${xiao}' and '${da}'")
    List<TCommodity>jiage(String category_name, String config_details, String attribute, int xiao, int da);

    @Select(" SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.release_time,a.category_id,e.category_name,a.commodity_picture,a.sales_volume\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and e.category_name like '%${category_name}%' and a.status=1 and a.commodity_price BETWEEN '${xiao}' and '${da}'")
    List<TCommodity>jiages(String category_name, String config_details, String attribute, int xiao, int da);



    //大小价格查询count
    @Select("SELECT   COUNT(DISTINCT a.id)\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and e.category_name like '%${category_name}%' and a.status=1 and a.commodity_price BETWEEN '${xiao}' and '${da}'")
    int jiage1s(String category_name, String config_details, String attribute, int xiao, int da);


    //价格低到高排序
    @Select("SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.release_time,a.category_id,e.category_name,a.commodity_picture,a.sales_volume\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and e.category_name = '${category_name}' and a.status=1 ORDER BY a.commodity_price asc")
    List<TCommodity>gao(String category_name, String config_details, String attribute);

    @Select("SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.release_time,a.category_id,e.category_name,a.commodity_picture,a.sales_volume\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and e.category_name like '%${category_name}%' and a.status=1 ORDER BY a.commodity_price asc")
    List<TCommodity>gao1(String category_name, String config_details, String attribute);

    //价格高到低排序
    @Select("\t\t\t\t \n" +
            "\t\t\t\t SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.release_time,a.category_id,e.category_name,a.commodity_picture,a.sales_volume\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and e.category_name = '${category_name}' and a.status=1 ORDER BY a.commodity_price desc")
    List<TCommodity>di(String category_name, String config_details, String attribute);

    @Select("\t\t\t\t \n" +
            "\t\t\t\t SELECT DISTINCT a.id,a.commodity_name,a.commodity_description,a.commodity_price,a.status,a.release_time,a.category_id,e.category_name,a.commodity_picture,a.sales_volume\n" +
            "\n" +
            "            FROM t_commodity a,  t_selected_attribute b,   t_category_attribute i,  t_attribute_details j,  `t_commodity_category` e,\n" +
            "\t\t\t\t\t\t\n" +
            "            t_selected_config h, t_category_config c,  t_config_details d\n" +
            "\t\t\t\t\t\t\n" +
            "           where a.category_id=e.id and \n" +
            "\t\t\t\t\t\t\t\t h.goods_id=a.id  and\n" +
            "\t\t\t\t\t\t\t\t h.config_id=c.id and\n" +
            "\t\t\t\t\t\t\t\t h.config_details_id=d.id and\n" +
            "\t\t\t\t\t       e.id=c.category_id  and\n" +
            "\t\t\t\t\t\t\t\t b.goods_id=a.id and \n" +
            "\t\t\t\t\t\t\t\t b.attribute_id=i.id and\n" +
            "\t\t\t\t\t\t\t\t b.attribute_details_id=j.id and\n" +
            "\t\t\t\t\t\t\t\t j.details like '%${attribute}%' and d.config_details like  '%${config_details}%' and e.category_name like '%${category_name}%' and a.status=1 ORDER BY a.commodity_price desc")
    List<TCommodity>di1(String category_name, String config_details, String attribute);


    //分类查询
    @Select("SELECT * from t_commodity_category")
    List<T_commodity_category>fl();

    //购物车数量
    @Select("SELECT count(*) from shopping_cart WHERE user_id='${userid}'")
    int gwc(int userid);
}
